JOIN na MSSQL
Otázka od: Miso
5. 5. 2004 10:04
Zdar,
..potrebujem join na vytvoreny stlpec, je to mozne ?
..nieco ako :
--------------------------
select a.fld, Patindex('%/%',a.fld) as index_sep,
uprav =
case
when Patindex('%/%',a.fld) = 0 then a.fld
when Patindex('%/%',a.fld) > 0 then
substring (a.fld, 1, Patindex('%/%',a.fld) -1 )
end
,
spojenie =
case
when t.newfld is null then 'nenasiel'
else t.newfld
end
from Table_XXX a
left join temp_val t
on uprav = t.oldfld
--------------------------
..hadze mi to vsak, ze : Invalid column name 'uprav'.
Dik, Miso
MSSQL 2000
Odpovedá: Svoboda Jiri
5. 5. 2004 10:46
Pokud to je MSSQL,tak nejjednoduzsi je tu prvni cast udelat jako vnoreny
select.
Asi tak nejak:
Select a.*,
spojenie =
case
when t.newfld is null then 'nenasiel'
else t.newfld
end
from
( select a.fld, Patindex('%/%',a.fld) as index_sep,
uprav =
case
when Patindex('%/%',a.fld) = 0 then a.fld
when Patindex('%/%',a.fld) > 0 then
substring (a.fld, 1, Patindex('%/%',a.fld) -1 )
end
from Table_XXX) as a
left join temp_val t
on a.uprav = t.oldfld
-----Original Message-----
From: delphi-l-owner@clexpert.cz [mailto:delphi-l-owner@clexpert.cz]On
Behalf Of Miso
Sent: Wednesday, May 05, 2004 10:50 AM
To: delphi-l@clexpert.cz
Subject: JOIN na MSSQL
Zdar,
..potrebujem join na vytvoreny stlpec, je to mozne ?
..nieco ako :
--------------------------
select a.fld, Patindex('%/%',a.fld) as index_sep,
uprav =
case
when Patindex('%/%',a.fld) = 0 then a.fld
when Patindex('%/%',a.fld) > 0 then
substring (a.fld, 1, Patindex('%/%',a.fld) -1 )
end
,
spojenie =
case
when t.newfld is null then 'nenasiel'
else t.newfld
end
from Table_XXX a
left join temp_val t
on uprav = t.oldfld
--------------------------
..hadze mi to vsak, ze : Invalid column name 'uprav'.
Dik, Miso
MSSQL 2000
Odpovedá: Lubomir Fule
7. 5. 2004 6:56
Alias vypocitavaneho stlpca nemozes pouzit v definicii podmienky. Musis to
urobit tak, ze ten vypocet uvedies aj v podmienke, teda takto:
select a.fld, Patindex('%/%',a.fld) as index_sep,
uprav =
case
when Patindex('%/%',a.fld) = 0 then a.fld
when Patindex('%/%',a.fld) > 0 then
substring (a.fld, 1, Patindex('%/%',a.fld) -1 )
end,
spojenie =
case
when t.newfld is null then 'nenasiel'
else t.newfld
end
from Table_XXX a
left join temp_val t
on t.oldfld = case
when Patindex('%/%',a.fld) = 0 then a.fld
when Patindex('%/%',a.fld) > 0 then substring (a.fld,
1, Patindex('%/%',a.fld) -1 )
end
> Zdar,
>
> ..potrebujem join na vytvoreny stlpec, je to mozne ?
> ..nieco ako :
> --------------------------
> select a.fld, Patindex('%/%',a.fld) as index_sep,
> uprav =
> case
> when Patindex('%/%',a.fld) = 0 then a.fld
> when Patindex('%/%',a.fld) > 0 then
> substring (a.fld, 1, Patindex('%/%',a.fld) -1 )
> end
> ,
> spojenie =
> case
> when t.newfld is null then 'nenasiel'
> else t.newfld
> end
> from Table_XXX a
>
> left join temp_val t
> on uprav = t.oldfld
> --------------------------
>
> ..hadze mi to vsak, ze : Invalid column name 'uprav'.